﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace ConsoleApplicationTool
{
    public class OledbUtil
    {
        private readonly OleDbConnection _connection;

        public OledbUtil(string filename)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
            {
                DataSource =filename, //Application.StartupPath+filename,
                Provider = "Microsoft.Jet.OLEDB.4.0"
            };
            _connection = new OleDbConnection(builder.ConnectionString);
        }
        /// <summary>
        ///  执行Sql语句,获取DataTable结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string sql)
        {
            _connection.Open();
            try
            {
                DataSet dataset = new DataSet();
                OleDbCommand com = new OleDbCommand(sql, _connection);
                OleDbDataAdapter da = new OleDbDataAdapter(com);
                da.Fill(dataset);
                return dataset.Tables[0];
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                _connection.Close();
            }
        }
        /// <summary>
        /// 获取结果集的第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int GetScalar(string sql)
        {
            _connection.Open();
            try
            {
                OleDbCommand com = new OleDbCommand(sql, _connection);
                int result = int.Parse(com.ExecuteScalar().ToString());
                return result;
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                _connection.Close();
            }
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>影响行数</returns>
        public int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
        {
            _connection.Open();
            try
            {
                OleDbCommand com = new OleDbCommand(SQLString, _connection);
                PrepareCommand(com, _connection, null, SQLString, cmdParms);
                int rows = com.ExecuteNonQuery();
                com.Parameters.Clear();
                return rows;
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                _connection.Close();
            }
        }
        private void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        {
            cmd.CommandText = cmdText;
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}
